import sqlite3
from contextlib import closing

DB_PATH = 'attendance.db'

def init_db():
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute('''
        CREATE TABLE IF NOT EXISTS attendance (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_name TEXT NOT NULL,
            job TEXT,
            date TEXT NOT NULL,
            type TEXT NOT NULL,
            status TEXT NOT NULL,
            remark TEXT
        )
        ''')
        conn.commit()

def insert_attendance(records):
    """
    插入考勤记录。
    如果是帮众名单(type=帮众名单)，只保留最新日期的名单，日期更早的名单不插入。
    """
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        # 先找出数据库中现有帮众名单的最大日期
        c.execute("SELECT MAX(date) FROM attendance WHERE type='帮众名单'")
        row = c.fetchone()
        db_max_date = row[0] if row and row[0] else None
        # 拆分帮众名单和普通考勤
        bangzhong_records = [r for r in records if r[3] == '帮众名单']
        other_records = [r for r in records if r[3] != '帮众名单']
        # 处理帮众名单
        if bangzhong_records:
            # 假设本次上传的帮众名单日期都相同，取第一个
            upload_date = bangzhong_records[0][2]
            # 只在上传日期比数据库中最大日期新时才replace
            if (db_max_date is None) or (upload_date > db_max_date):
                # 删除所有旧帮众名单
                c.execute("DELETE FROM attendance WHERE type='帮众名单'")
                # 插入新名单
                c.executemany('''
                    INSERT INTO attendance (player_name, job, date, type, status, remark)
                    VALUES (?, ?, ?, ?, ?, ?)
                ''', bangzhong_records)
        # 普通考勤直接插入
        if other_records:
            c.executemany('''
                INSERT INTO attendance (player_name, job, date, type, status, remark)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', other_records)
        conn.commit()

def get_all_attendance():
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute('SELECT player_name, job, date, type, status, remark FROM attendance')
        return c.fetchall()

def get_latest_bangzhong_date():
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute("SELECT MAX(date) FROM attendance WHERE type='帮众名单'")
        row = c.fetchone()
        return row[0] if row and row[0] else None

def get_latest_bangzhong_list():
    """返回最新帮众名单的所有成员 (player_name, job) 列表"""
    latest_date = get_latest_bangzhong_date()
    if not latest_date:
        return []
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute("SELECT player_name, job FROM attendance WHERE type='帮众名单' AND date=?", (latest_date,))
        return c.fetchall()

def update_attendance(player_name, date, type_, status):
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        # 先尝试更新
        c.execute('''
            UPDATE attendance SET status=? WHERE player_name=? AND date=? AND type=?
        ''', (status, player_name, date, type_))
        if c.rowcount == 0:
            # 没有记录则插入，job从最新帮众名单查，没有就设为''
            job = ''
            try:
                c2 = conn.cursor()
                c2.execute("SELECT job FROM attendance WHERE player_name=? AND type='帮众名单' ORDER BY date DESC LIMIT 1", (player_name,))
                row = c2.fetchone()
                if row and row[0]:
                    job = row[0]
            except Exception:
                pass
            c.execute('''
                INSERT INTO attendance (player_name, job, date, type, status, remark)
                VALUES (?, ?, ?, ?, ?, '')
            ''', (player_name, job, date, type_, status))
        conn.commit()

def delete_attendance_by_date_and_type(date, type_):
    """删除指定日期和类型的所有考勤记录"""
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute('DELETE FROM attendance WHERE date=? AND type=?', (date, type_))
        conn.commit()

def delete_all_bangzhong():
    """删除所有帮众名单记录"""
    with closing(sqlite3.connect(DB_PATH)) as conn:
        c = conn.cursor()
        c.execute("DELETE FROM attendance WHERE type='帮众名单'")
        conn.commit()
